package com.xsy.middleware_template.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xsy.middleware_template.utils.ResultVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName TestController
 * @Description
 * @Author dzf
 * @Date 2022/12/8 15:09
 * @Version 1.0
 */
@RestController
@RequestMapping("/kis")
public class KisController {

    private final static Logger logger = LoggerFactory.getLogger(KisController.class);

    @Autowired(required = false)
    @Qualifier("secondaryJdbcTemplate")
    JdbcTemplate jdbcTemplate;

    /**
     * 数据查询
     *
     * @return
     */
    @PostMapping(value = "/getData", produces = "application/json;charset=UTF-8")
    public ResultVo getData(@RequestParam(name = "sql") String sql) {
        try {
            logger.info("sql：" + sql);
            //模糊查询使用@代替，否则接口将会报错
            sql = sql.replaceAll("@", "%");
            List<Map<String, Object>> queryList = jdbcTemplate.queryForList(sql);
            return new ResultVo(200, "查询成功", queryList);
        } catch (Exception e) {
            e.printStackTrace();
            return new ResultVo(500, "接口执行异常：" + e.getMessage());
        }
    }

    /**
     * 执行sql
     **/
    @PostMapping(value = "/exceSql", produces = "application/json;charset=UTF-8")
    public ResultVo exceSql(@RequestParam(name = "sql") String sql) {
        try {
            logger.info("sql：" + sql);
            //模糊查询使用@代替，否则接口将会报错
            sql = sql.replaceAll("@", "%");
            jdbcTemplate.execute(sql);
            return new ResultVo(200, "执行成功");
        } catch (Exception e) {
            e.printStackTrace();
            return new ResultVo(500, "接口执行异常：" + e.getMessage());
        }
    }

//     jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
//        @Override
//        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
//            preparedStatement.setString(1,test.get(0));
//        }
//
//        @Override
//        public int getBatchSize() {
//            return 10;
//        }
//    });


    /**
     * process : 函数名称
     * 存储过程调用
     */
    @PostMapping(value = "/exceProcess", produces = "application/json;charset=UTF-8")
    public ResultVo exceProcess(@RequestBody JSONObject data) {
        /**
         * 每个存储过程参数含义
         * name: 参数名
         * index: 参数下标
         * type： 输入输出类型
         * value: 参数类容
         * code: java中参数类型
         */
        try {
            String process = data.getString("process");
            JSONArray params = data.getJSONArray("params");
            if (StringUtils.isEmpty(process)) {
                return new ResultVo(500, "函数名称不能为空", null);
            }
            //获取存储过程参数
            List<SqlParameter> parameters = new ArrayList<>();
            Map<Integer, Object> valueMap = new HashMap<>();
            Map<Integer, Integer> codeMap = new HashMap<>();
            Map<Integer, String> typeMap = new HashMap<>();
            if (params != null && params.size() > 0) {
                StringBuilder sbf = new StringBuilder();
                params.forEach(param -> {
                    String str = JSONObject.toJSONString(param);
                    JSONObject e = JSONObject.parseObject(str, JSONObject.class);
                    if ("in".equals(e.getString("type"))) {
                        parameters.add(new SqlParameter(e.getString("name"), e.getInteger("index")));
                        valueMap.put(e.getInteger("index"), e.get("value"));
                    } else if ("out".equals(e.getString("type"))) {
                        parameters.add(new SqlOutParameter(e.getString("name"), e.getInteger("index")));
                        valueMap.put(e.getInteger("index"), e.get("code"));
                    }
                    codeMap.put(e.getInteger("index"), e.getInteger("code"));
                    typeMap.put(e.getInteger("index"), e.getString("type"));
                    sbf.append("?,");
                });
                String names = sbf.toString().substring(0, sbf.toString().length() - 1);
                process = "{call " + process + "(" + names + ")}";
            } else {
                process = "{call " + process + "()}";
            }

            String finalProcess = process;
            Map<String, Object> execute = jdbcTemplate.call(
                    new CallableStatementCreator() {
                        @Override
                        public CallableStatement createCallableStatement(Connection con) throws SQLException {
                            CallableStatement callableStatement = con.prepareCall(finalProcess);
                            valueMap.forEach((k, v) -> {
                                if ("in".equals(typeMap.get(k))) {
                                    if (codeMap.get(k).equals(Types.INTEGER)) {
                                        try {
                                            callableStatement.setInt(k, (Integer) v);
                                        } catch (SQLException throwables) {
                                            throw new RuntimeException("下标为[" + k + "]参数,接口中设置数据类型异常");
                                        }
                                    } else if (codeMap.get(k).equals(Types.VARCHAR)) {
                                        try {
                                            callableStatement.setString(k, String.valueOf(v));
                                        } catch (SQLException throwables) {
                                            throw new RuntimeException("下标为[" + k + "]参数,接口中设置数据类型异常");
                                        }
                                    }else if (codeMap.get(k).equals(Types.DOUBLE)) {
                                        try {
                                            callableStatement.setDouble(k, Double.valueOf(v.toString()));
                                        } catch (SQLException throwables) {
                                            throw new RuntimeException("下标为[" + k + "]参数,接口中设置数据类型异常");
                                        }
                                    }else if (codeMap.get(k).equals(Types.DATE)) {
                                        try {
                                            Date date = new Date(Long.valueOf(v.toString()));
                                            callableStatement.setDate(k,date);
                                        } catch (SQLException throwables) {
                                            throw new RuntimeException("下标为[" + k + "]参数,接口中设置数据类型异常");
                                        }
                                    }
                                    else {
                                        throw new RuntimeException("下标为[" + k + "]参数,接口中未配置此数据类型");
                                    }
                                } else {
                                    try {
                                        callableStatement.registerOutParameter(k, codeMap.get(k));
                                    } catch (SQLException throwables) {
                                        throw new RuntimeException("下标为[" + k + "]参数,接口中设置数据类型异常");
                                    }
                                }
                            });
                            return callableStatement;
                        }
                    }, parameters);
            return new ResultVo(200, "存储过程执行完成", execute);
        } catch (Exception e) {
            e.printStackTrace();
            return new ResultVo(500, "接口执行异常：" + e.getMessage());
        }
    }


}
